package bainuo

import groovy.sql.Sql

def execute() {
    def conn = null
    try {
        // 创建数据库连接
        conn = groovy.sql.Sql.newInstance(mypara.hqcon(1))
        List<Map<String, String>> results = new ArrayList<>()
        def sql = "select top 10 id, message, time from wms_bainuo_log"
        def List = conn.rows(sql)

        for (def item : List) {
            if (item != null) {
                Map<String, String> items = new HashMap<>();
                items.put("record_time", item.time.toString());
                items.put("record_logid", item.id.toString());
                items.put("record_message", item.message.toString());
                results.add(items);
            }
        }
        Map<String, List> pushData = new HashMap<>()
        pushData.put("items", results);
        println(pushData)
        push(pushData)
    } catch (Exception e) {
        e.printStackTrace()
    } finally {
        if (conn != null) {
            conn.close()
        }
    }
}

def push(data) {
    println "-------------------------mysql & sql server-------------------------"
    try {
        // 请求信息 校验
        def items = data.get("items")
        for (def item : items) {
            def recordTime = item.get("record_time")
            def recordLogid = item.get("record_logid")
            def recordMessage = item.get("record_message")
            def params = [:]
            params.put("record_time", recordTime)
            params.put("record_logid", recordLogid)
            params.put("record_message", recordMessage)
            insertLog(params)
        }
        println "-------------------------传输完成-------------------------"
    } catch (Exception e) {
        e.printStackTrace();
        return ["state": 0, "Ascript": "请求参数错误,请检查"]
    }
}

static def insertLog(params) {
    def conn = null
    def insertKeys = [] // 字段
    def insertValues = [] // 字段值
    def placeholders = [] // 占位符

    try {
        for (def p : params) {
            insertKeys.add(p.key)
            insertValues.add(p.value)
            placeholders.add('?')
        }

        // 构建连接字符串
        def dbUrl = "jdbc:mysql://10.1.1.87:3306/test"
        def user = "oper_dc"
        def password = "operzADF2020qwer"

        // 建立数据库连接
        conn = Sql.newInstance(dbUrl, user, password, "com.mysql.cj.jdbc.Driver")

        // 检查 record_logid 是否已存在
        def checkSql = "SELECT COUNT(*) AS count FROM time_records WHERE record_logid = ?"
        def logIdExists = conn.firstRow(checkSql, [params.record_logid])?.count ?: 0

        if (logIdExists == 0) {
            // 构建插入语句
            def sqlStatement = "INSERT INTO time_records(" + insertKeys.join(",") + ") VALUES (" + placeholders.join(",") + ")"
            // 执行插入操作
            conn.execute(sqlStatement, insertValues)
        } else {
            println "数据已经存在，无需再次插入"
            println "Record with logid :[${params.record_logid}] already exists, skipping insertion."
        }
    } catch (Exception e) {
        e.printStackTrace()
    } finally {
        if (conn != null) {
            conn.close()
        }
    }
}